package com.zuipin.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import freemarker.template.TemplateExceptionHandler;

/**
 * 生成代码工具类
  * @ClassName: GenerationCodeUtil
  * @Description: TODO
  * @author joy.zhang
  * @date 2017年3月29日 上午9:12:10
  *
 */
@Component
@Order(value = 99)
public class GenerationCodeUtil implements CommandLineRunner {
	
	@Resource
	private DataSource dataSource;
	
	/**
	  * 生成代码
	  * @Title: createCode4DB
	  * @author joy.zhang
	  * @date 2017年3月31日 下午2:56:35
	  * @param createController 生成Controller
	  * @param createEntity 生成Entity
	  * @param createMapper 生成Mapper和xml
	  * @param createService 生成Service和impl
	  * @param talbles 生成表范围，表名后面必须价逗号
	  * @param isTest 是否测试 是不会生成文件将内容输出到控制台， 否生成文件
	  * @throws SQLException
	  * @return void
	 */
	public void createCode4DB(Boolean createController, Boolean createEntity, Boolean createMapper, Boolean createService, String talbles, Boolean isTest) throws SQLException {
		Connection conn = dataSource.getConnection();
		// 连接数据库 读取表实体类结构
		List<TableEntity> tabList = connDB(conn, talbles, isTest);
		
		// 字段映射处理，关联关系处理
		List<ObEntity> oeList = null;
		if (tabList != null) {
			oeList = ObEntity.convert2ObEntityList(tabList);
		}
		
		// 生成代码
		try {
			createCode4Entity(oeList, createController, createEntity, createMapper, createService, isTest);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (TemplateException e) {
			e.printStackTrace();
		}
		if (conn != null && !conn.isClosed()) {
			conn.close();
		}
	}
	
	/**
	 * 通过实体类生成代码方法，主要是一些固定参数的组装，然后调用写入文件方法
	  *  @Title: createCode4Entity
	  * @author joy.zhang
	  * @date 2017年3月31日 下午4:32:32
	  * @param obeList
	  * @param createController
	  * @param createEntity
	  * @param createMapper
	  * @param createService
	  * @param isTest
	  * @throws IOException
	  * @throws TemplateException
	  * @return void
	 */
	private static void createCode4Entity(List<ObEntity> obeList, Boolean createController, Boolean createEntity, Boolean createMapper, Boolean createService, Boolean isTest)
			throws IOException, TemplateException {
		File file = ResourceUtils.getFile("classpath:ftl");
		if (file == null) {
			throw new FileNotFoundException("ftl模板文件夹不存在");
		}
		String path = file.getParentFile().getParentFile().getParentFile().getPath();
		System.out.println(path);
		
		Configuration cfg = new Configuration(Configuration.VERSION_2_3_22);
		cfg.setDirectoryForTemplateLoading(file);
		cfg.setDefaultEncoding("UTF-8");
		cfg.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER);
		cfg.unsetCacheStorage();
		
		// update by zjj
		// String basePath = "e:\\auto";
		// String basePath = path + "/src/main/java/com/zuipin/";
		
		String basePath = "e:\\auto";
		// 判断是否已经存在， 读取实体类
		String entityPath = "entity";
		String suffix = ".java";
		String prefix = "";
		if (createEntity) {
			writeFile(cfg, basePath + entityPath, obeList, entityPath + ".ftl", prefix, suffix, isTest);
		}
		// 判断是否已经存在， 生成mapper和xml
		if (createMapper) {
			entityPath = "mapper";
			writeFile(cfg, basePath + entityPath, obeList, entityPath + ".ftl", prefix, "Mapper" + suffix, isTest);
			entityPath = "xml";
			writeFile(cfg, basePath + "mapper/conf", obeList, entityPath + ".ftl", prefix, "Mapper.xml", isTest);
		}
		// 判断是否已经存在，生成service和impl
		if (createService) {
			entityPath = "service";
			prefix = "I";
			writeFile(cfg, basePath + entityPath, obeList, entityPath + ".ftl", prefix, "Service" + suffix, isTest);
			
			entityPath = "impl";
			prefix = "";
			writeFile(cfg, basePath + "service/" + entityPath, obeList, "serviceimpl.ftl", prefix, "ServiceImpl" + suffix, isTest);
		}
		// 判断是否已经存在，生成controller
		if (createController) {
			entityPath = "controller";
			writeFile(cfg, basePath + entityPath, obeList, entityPath + ".ftl", prefix, "Controller" + suffix, isTest);
		}
	}
	
	/**
	 * 连接到数据库并返回表信息
	  *  @Title: connDB
	  * @author joy.zhang
	  * @date 2017年3月31日 下午4:31:52
	  * @param conn
	  * @param talbles
	  * @param isTest
	  * @return
	  * @throws SQLException
	  * @return List<TableEntity>
	 */
	public List<TableEntity> connDB(Connection conn, String talbles, Boolean isTest) throws SQLException {
		// 2. 下面就是获取表的信息。
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet tableRet = dbMetaData.getTables(null, "%", "%", new String[] { "TABLE" });
		/*其中"%"就是表示*的意思，也就是任意所有的意思。其中m_TableName就是要获取的数据表的名字，如果想获取所有的表的名字，就可以使用"%"来作为参数了。*/
		List<TableEntity> tabList = new ArrayList<TableEntity>();
		// 3. 提取表的名字。
		while (tableRet.next()) {
			
			String tableName = tableRet.getString("TABLE_NAME");
			String tabType = tableRet.getString("TABLE_TYPE");
			String tabRemarks = tableRet.getString("REMARKS");
			if (tabRemarks == null || "".equals(tabRemarks)) {
				tabRemarks = getCommentByTableName(tableName);
			}
			if (tabType != null && tabType.trim().toUpperCase().equals("TABLE") && (talbles == null || talbles.indexOf(tableName.toLowerCase() + ",") > -1)) {
				if (isTest) {
					System.out.println(tableRet.getString("TABLE_NAME"));
				}
				List<ColumnEntity> columnlist = new ArrayList<ColumnEntity>();
				
				// 提取表主键
				Map<String, Object> pkMap = new HashMap<String, Object>();
				ResultSet primaryKeyResultSet = dbMetaData.getPrimaryKeys(null, null, tableName);
				while (primaryKeyResultSet.next()) {
					String primaryKeyColumnName = primaryKeyResultSet.getString("COLUMN_NAME");
					pkMap.put(primaryKeyColumnName, primaryKeyColumnName);
				}
				
				// 4. 提取表内的字段的名字和类型
				ResultSet colRet = dbMetaData.getColumns(null, "%", tableName, "%");
				while (colRet.next()) {
					String columnName = colRet.getString("COLUMN_NAME");
					String columnType = colRet.getString("TYPE_NAME");
					int datasize = colRet.getInt("COLUMN_SIZE");
					int digits = colRet.getInt("DECIMAL_DIGITS");
					int nullable = colRet.getInt("NULLABLE");
					String isAutoincrement = colRet.getString("IS_AUTOINCREMENT");
					String remarks = colRet.getString("REMARKS");
					if (isTest) {
						System.out.println(columnName + " " + columnType + " " + datasize + " " + digits + " " + nullable + " " + isAutoincrement + " " + remarks);
					}
					ColumnEntity ce = new ColumnEntity();
					ce.setColumnName(columnName);
					ce.setColumnType(columnType);
					ce.setDatasize(datasize);
					ce.setDigits(digits);
					ce.setAutoincrement(isAutoincrement);
					ce.setNullable(nullable);
					ce.setRemarks(remarks);
					if (pkMap.containsKey(columnName)) {
						ce.setPkType(1);
					}
					columnlist.add(ce);
				}
				
				TableEntity te = new TableEntity();
				te.setTalbleName(tableName);
				te.setColumnlist(columnlist);
				te.setRemarks(tabRemarks);
				tabList.add(te);
			}
		}
		
		return tabList;
		
	}
	
	/**
	 * 执行写入文件,测试时写出到控制台
	  *  @Title: writeFile
	  * @author joy.zhang
	  * @date 2017年3月31日 下午4:31:26
	  * @param cfg
	  * @param entityPath
	  * @param obeList
	  * @param tempName
	  * @param prefix
	  * @param suffix
	  * @param isTest
	  * @throws TemplateException
	  * @throws IOException
	  * @return void
	 */
	public static void writeFile(Configuration cfg, String entityPath, List<ObEntity> obeList, String tempName, String prefix, String suffix, Boolean isTest)
			throws TemplateException, IOException {
		Template temp = cfg.getTemplate(tempName, "UTF-8");
		for (ObEntity ob : obeList) {
			
			// 判断文件是否已经存在
			File obfile = new File(entityPath, prefix + ob.getClassName() + suffix);
			
			if (isTest == null || isTest) {
				Writer out = new OutputStreamWriter(System.out, "UTF-8");
				temp.process(ob, out);
				out.flush();
				// out.close();
			} else if (!obfile.exists()) {
				System.out.println(obfile.getPath() + " create !");
				obfile.createNewFile();
				OutputStream fos = null;
				try {
					fos = new FileOutputStream(obfile);
					Writer out = new OutputStreamWriter(fos, "UTF-8");
					temp.process(ob, out);
					fos.flush();
				} catch (IOException e) {
					e.printStackTrace();
				} finally {
					if (fos != null) {
						fos.close();
					}
				}
			} else {
				System.out.println(obfile.getPath() + " exists!");
			}
		}
	}
	
	/** 
	 * 获得某表的建表语句 
	 * @param tableName 
	 * @return 
	 * @throws Exception 
	 */
	public String getCommentByTableName(String tableName) {
		String comment = "";
		
		Connection conn = null;
		try {
			conn = dataSource.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName);
			if (rs != null && rs.next()) {
				String createDDL = rs.getString(2);
				comment = parse(createDDL);
				
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (comment == null) {
			comment = "";
		}
		return comment;
	}
	
	/** 
	 * 返回注释信息 
	 * @param all 
	 * @return 
	 */
	public static String parse(String all) {
		String comment = null;
		int index = all.indexOf("COMMENT='");
		if (index < 0) {
			return "";
		}
		comment = all.substring(index + 9);
		comment = comment.substring(0, comment.length() - 1);
		return comment;
	}
	
	@SuppressWarnings("unchecked")
	private static Map<String, String> convertMap(Map<String, Object> dataMap, Map<String, String> resultMap, String key) {
		for (Entry<String, Object> ent : dataMap.entrySet()) {
			if (ent != null) {
				if (ent.getValue() instanceof Map) {
					if (key != null && !key.equals("")) {
						convertMap((Map<String, Object>) ent.getValue(), resultMap, key + "." + ent.getKey());
					} else {
						convertMap((Map<String, Object>) ent.getValue(), resultMap, ent.getKey());
					}
				} else {
					if (key != null && !key.equals("")) {
						resultMap.put(key + "." + ent.getKey(), ent.getValue().toString());
					} else {
						resultMap.put(ent.getKey(), ent.getValue().toString());
					}
					
				}
			}
		}
		return resultMap;
	}
	
	/**
	 * 启动后自动执行
	 */
	@Override
	public void run(String... args) throws Exception {
		// createCode4DB(true, true, true, true, "product_copy,", false);
	}
	
	public static void main(String... args) throws Exception {
		
		// 测试环境编译过不了 需要使用清打开注释
		// Map<String, String> map = new HashMap<String, String>();
		// try {
		// org.yaml.snakeyaml.Yaml yaml = new org.yaml.snakeyaml.Yaml();
		//
		// File file = ResourceUtils.getFile("classpath:application.yml");
		// if (file != null && file.canRead()) {
		// //也可以将值转换为Map
		// // map = (Map) yaml.load(new FileInputStream(file));
		// @SuppressWarnings("unchecked")
		// Map<String, Object> dataMap = (Map<String, Object>) yaml
		// .load(new FileInputStream(file));
		// System.out.println(dataMap);
		// //通过map我们取值就可以了.
		// map = convertMap(dataMap, map, "");
		// }
		// } catch (Exception e) {
		// e.printStackTrace();
		// }
		//
		// if (map != null && map.containsKey("spring.datasource.url")) {
		//
		// //创建数据源
		// DataSourceBuilder dsBuilder = DataSourceBuilder.create();
		// dsBuilder.url(map.get("spring.datasource.url"));
		// dsBuilder.driverClassName(map.get("spring.datasource.driver-class-name"));
		// dsBuilder.username(map.get("spring.datasource.username"));
		// dsBuilder.password(map.get("spring.datasource.password"));
		// dsBuilder.type(org.apache.commons.dbcp.BasicDataSource.class);
		// DataSource dataSource = dsBuilder.build();
		//
		// //执行生成
		// GenerationCodeUtil gcu = new GenerationCodeUtil();
		// gcu.dataSource = dataSource;
		//
		// gcu.createCode4DB(true, true, true, true, "product_copy,", false);
		// } else {
		// System.out.println("读取数据库配置失败");
		// }
		// 测试环境编译过不了 需要使用清打开注释
		// 创建数据源
		
		DataSourceBuilder dsBuilder = DataSourceBuilder.create();
		dsBuilder.url("jdbc:mysql://localhost/liuqi?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false");
		dsBuilder.driverClassName("com.mysql.jdbc.Driver");
		dsBuilder.username("root");
		dsBuilder.password("root");
		dsBuilder.type(org.apache.commons.dbcp.BasicDataSource.class);
		DataSource dataSource = dsBuilder.build();
		// 执行生成
		GenerationCodeUtil gcu = new GenerationCodeUtil();
		gcu.dataSource = dataSource;
		gcu.createCode4DB(true, true, true, true, "sys_user,", false);
		
	}
	
}
